using System;
using System.Data; 
using System.Data.Odbc; 
using System.Data.SqlClient; 
using System.Data.OleDb; 
using System.Data.Common;
using System.Collections;
using System.Diagnostics;
using PIM.Utilities.ConfigurationStore;
using System.Threading;
using Oracle.DataAccess.Client ;
using PIM.Utilities;
using PIM.Utilities.Logging;
using System.Text.RegularExpressions;
using System.Text;
using PIM.Utilities.ConfigurationStore;

namespace PIM.Utilities.DataAccess

{
	public class PIMManager
	{
		#region Constants

		private const string _PROVIDERHIVE = @"SOFTWARE\ODBC\ODBC.INI";
        private const string _PROVIDER64HIVE = @"SOFTWARE\WOW6432NODE\ODBC\ODBC.INI";

		private const int COMMAND_TIMEOUT = 60000;//seconds

		#endregion 

		#region Queries
		private const string GET_NEW_ID = "SELECT QSISNULL(MAX({0}), 0) + 1 FROM  {1}";
		private const string SELECT_INFORMATION_SCHEMA = "SELECT TABLE_NAME AS NAME FROM INFORMATION_SCHEMA.TABLES";
        private const string SELECT_INFORMATION_SCHEMA_ISEXIST = "SELECT COUNT(*) FROM (SELECT TABLE_NAME AS NAME FROM INFORMATION_SCHEMA.TABLES) [NAME]  WHERE UPPER(NAME) = '{0}' " ;
		private const string SELECT_USER_TABLES = "SELECT TABLE_NAME AS NAME FROM USER_TABLES UNION SELECT VIEW_NAME FROM USER_VIEWS";
        private const string SELECT_USER_TABLES_ISEXIST = "SELECT COUNT(*) FROM (SELECT TABLE_NAME AS NAME FROM USER_TABLES UNION SELECT VIEW_NAME FROM USER_VIEWS) WHERE UPPER(NAME) = '{0}'";
		private const string SELECT_COLUMNS = "SELECT * FROM {0} WHERE 1=2";
        private const string SELECT_USER_CONSTRAINT_ISEXIST = "SELECT COUNT(*) FROM USER_CONSTRAINTS WHERE UPPER(TABLE_NAME) = '{0}' AND CONSTRAINT_TYPE = 'P'";
        private const string SELECT_user_CONSTRAINT_NAME = "SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE UPPER(TABLE_NAME) = '{0}' AND CONSTRAINT_TYPE = 'P'";
        private const string SELECT_INFORMATION_CONSTRAINT_ISEXIST = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE UPPER(TABLE_NAME) = '{0}'";
        private const string SELECT_INFORMATION_CONSTRAINT_NAME = "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE UPPER(TABLE_NAME) = '{0}'";
        private const string SELECT_INFORMATION_SCHEMA_COULMNS = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE UPPER(TABLE_NAME) = '{0}' ORDER BY COLUMN_NAME";
        private const string SELECT_USER_TABLES_COUMNS = "SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE UPPER(TABLE_NAME) = '{0}' ORDER BY COLUMN_NAME";
        private const string SELECT_INFORMATION_SCHEMA_COULMNS_ISEXIST = "SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE UPPER(TABLE_NAME) = '{0}' AND UPPER(COLUMN_NAME) = '{1}'";
        private const string SELECT_USER_TABLES_COUMNS_ISEXIST = "SELECT COUNT(COLUMN_NAME) FROM USER_TAB_COLUMNS WHERE UPPER(TABLE_NAME) = '{0}' AND UPPER(COLUMN_NAME) = '{1}'";
        private const string SELECT_INFORMATION_CONSTRAINT_COLUMN_NAME = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE UPPER(TABLE_NAME) = '{0}'";
        private const string SELECT_USER_CONSTRAINT_COLUMN_NAME = "SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'";
        private const string SELECT_INFORMATION_TABLE_COLUMN = "SELECT COLUMN_NAME,DATA_TYPE,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE UPPER(TABLE_NAME) = '{0}'";
        private const string SELECT_USER_TABLE_COLUMN = "SELECT COLUMN_NAME,DATA_TYPE,NULLABLE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE FROM USER_TAB_COLUMNS WHERE UPPER(TABLE_NAME) = '{0}'";
        private const string SELECT_USER_TABLES_IsView = "SELECT Count(VIEW_NAME) FROM USER_VIEWS WHERE UPPER(VIEW_NAME) = '{0}'";
        private const string SELECT_INFORMATION_SCHEMA_IsView = "SELECT COUNT(*) AS NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' AND UPPER(TABLE_NAME) = '{0}'";
        private const string INS_PROFILE_LOG_SQL = "INSERT INTO DBTRACE (TIME, APPLICATION, QUERY, PARAMETERS, ELPASEDTIME, CONTEXT, EXCEPTION, STATUS, PROCESSID, PROCESSNAME, MACHINENAME, INFO1) VALUES (@TIME, @APPLICATION, @QUERY, @PARAMETER, @ELPASEDTIME, @CONTEXT, @EXCEPTION, @STATUS, @PROCESSID, @PROCESSNAME, @MACHINENAME, @INFO1) ";
        private const string INS_PROFILE_LOG_ORA = "INSERT INTO DBTRACE (ID, TIME, APPLICATION, QUERY, PARAMETERS, ELPASEDTIME, CONTEXT, EXCEPTION, STATUS, PROCESSID, PROCESSNAME, MACHINENAME, INFO1) VALUES (DBTRACE_SEQ.nextval, :TIME, :APPLICATION, :QUERY, :PARAMETER, :ELPASEDTIME, :CONTEXT, :EXCEPTION, :STATUS, :PROCESSID, :PROCESSNAME, :MACHINENAME, :INFO1) ";
        #endregion

		#region Member Variables
		private IDbConnection _objConnection = null;
		private IDbTransaction _objTrans = null;
		private IDbCommand _objCommand = null;
		private static Hashtable refCursorTable = new Hashtable();

		private bool TransON = false;
		private string _connectionString = string.Empty;
		private string _dataSourceProvider;
		private DBProvider _structdataSourceProvider;
		private string _dataSourceName;
		private string _databaseName;
        private string _DataFile;
		private string _dsnPointedDatabaseName;
		private string _serverName;
		private string _userID;
		private string _password;
        private string _Context = string.Empty;
        private bool _SQLTraceOn = false;
	    private string _TraceValue = string.Empty;
        private DateTime dtTraceStartDate;
	    private string _lastException = string.Empty;
        
		//When connecting to SQL Server using .Net SQL Provider, the application name is appended to the connection string
		//This name will be logged and displayed as the name of the client application in various SQL server utlilites like 
		//enterprise manager, SQL Profiler etc. This helps in debugging. The application that uses DB manager can 
		//set this name as required before opening the connection. 
		private string _applicationName = string.Empty;
		private static string _applicationNameGlobal = "SAFE";

		private bool _enableConnectionPooling = true;

		#endregion 

		# region Properties.
		public DBProvider DBPROVIDER
		{
			get
			{
				return _structdataSourceProvider;
			}
		}

		public ConnectionState ConnectionStatus
		{
			get
			{
				ConnectionState conState = ConnectionState.Broken;
				if(_objConnection != null)
				{
					conState = _objConnection.State; 
				}
				return conState;
			}
		}

		public bool IsInTransaction
		{
			get {return TransON;}
		}

        public IDbConnection DBConnection
        {
            get
            {
                return _objConnection;
            }
        }

		public DBProvider DataProvider
		{
			get{return _structdataSourceProvider;}
		}

		public static string ApplicationNameGlobal
		{
			get { return _applicationNameGlobal; }
			set { _applicationNameGlobal = value; }
		}

		public string ApplicationName
		{
			get 
			{ 
				if (_applicationName == null || _applicationName.Length == 0) 
					return ApplicationNameGlobal;
				else
					return _applicationName;
			}
			set { _applicationName = value; }
		}

        public string Context
        {
            get { return _Context; }
            set { _Context = value; }
        }


		# endregion

		#region Constructors

        private int _dbConnectionTimeout = 0;
        private int _dbCommandTimeout = 0;
        private const string DBCONNECTIONTIMEOUT = "DBCONNECTIONTIMEOUT";
        private const string DBCOMMANDTIMEOUT = "DBCOMMANDTIMEOUT";
        private PIMManager()
        {
            _dbConnectionTimeout = GetTimeOut(DBCONNECTIONTIMEOUT);
            _dbCommandTimeout = GetTimeOut(DBCOMMANDTIMEOUT);
        }


		/// <summary>
		/// This Constructer can used for SQL Server, MS Access etc, not for Progress Database
		/// </summary>
		/// <param name="DataSourceName">System DSN Name</param>
		/// <param name="UserID">User ID</param>
		/// <param name="Password">Password</param>
		public PIMManager(string DataSourceName, string UserID, string Password) : this(DataSourceName, "", UserID, Password)
		{
			
		}
				
		public PIMManager(string configName, bool enableConnectionPooling) : this(configName)
		{
			_enableConnectionPooling = enableConnectionPooling;
		}
        public PIMManager(string ConfigName) : this()
		{
			try
			{
				QSecureConfig config = new QSecureConfig();
                string val = ConfigName;
                if (ConfigName.StartsWith("SAFE")) val = config.GetValue(ConfigName, "");
                if (val.ToUpper().IndexOf("ENABLETRACE=") > 0)
                {
                    _TraceValue = val.Substring(val.ToUpper().IndexOf("ENABLETRACE=") + 12, (val.Length-(val.ToUpper().IndexOf("ENABLETRACE=")+12)));
                    _SQLTraceOn = (_TraceValue == "0" )? false : true;
                    val = val.Substring(0, val.ToUpper().IndexOf("ENABLETRACE="));
                }
                ParseConnString(val, out _structdataSourceProvider, out _connectionString);
			}
			catch(Exception err)
			{
				GlobalLog.Logger.LogError(err.Message);
			}
		}

        //The connection string passed to this static method should include the 
        //DB type in the beginning. Else this will throw an exception
        //e.g. : SQLServer:Server=machine;Database=dbname;UID=username;PWD=password
        //e.g. : Oracle:Data Source=tnsname;User Id=username;Password=password
        public static PIMManager GetQSecuredbManager(string connstrWithDBType)
        {
            try
            {
                DBProvider provider;
                string connString;
                ParseConnString(connstrWithDBType, out provider, out connString);
                return new PIMManager(provider, connString);
            }
            catch (Exception err)
            {
                GlobalLog.Logger.LogError(err.Message);
                return null;
            }
        }
        public PIMManager(string configName, StringBuilder ctx)
            : this(configName)
        {
            _Context = ctx.ToString();
        }


        public static void ParseConnString(string connStringWithDBType,
            out DBProvider provider, out string connString)
        {
             if (connStringWithDBType == null || 
                connStringWithDBType.IndexOf(':') == -1)
            {
                provider = DBProvider.Null;
                connString = string.Empty;
                return;
            }

            string[] tokens = connStringWithDBType.Split((":").ToCharArray(), 2);
            connString = tokens[1];
            switch (tokens[0])
            {
                case "SQLServer":
                    {
                        provider = DBProvider.SQLServer;
                        break;
                    }
                case "Oracle":
                    {

                        provider = DBProvider.Oracle;
                        break;
                    }
                case "ODBC":
                    {
                        provider = DBProvider.ODBC;
                        break;
                    }
                case "OLEDB":
                    {
                        provider = DBProvider.OLEDB;
                        break;
                    }
                case "MSAccess":
                    {
                        provider = DBProvider.MSAccess;
                        break;
                    }
                case "Progress":
                    {
                        provider = DBProvider.Progress;
                        break;
                    }
                case "Paradox":
                    {
                        provider = DBProvider.Paradox;
                        break;
                    }
                default:
                    {
                        provider = DBProvider.Null;
                        break;
                    }
            }
            
        }

		/// <summary>
		///	This Constructer must used for Progress Database
		/// </summary>
		/// <param name="DataSourceName">System DSN Name for Progress Database</param>
		/// <param name="DatabaseName">Database Name for Progress Database</param>
		/// <param name="UserID">User ID for Progress Database</param>
		/// <param name="Password">Password For progress Database</param>
		public PIMManager(string DataSourceName, string DatabaseName, string UserID, string Password)	: this()
		{
			try
			{				
				_dataSourceName = DataSourceName; 
				_databaseName = DatabaseName;
				_userID = UserID;
				_password = Password;
				_structdataSourceProvider = GetProvider(_dataSourceName);    
				this.PrepareConnectionString();
			}	
			catch(Exception err)
			{
				GlobalLog.Logger.LogError(err.Message);
			}
		}

        public PIMManager(string SQLExpressDataFile, string DatabaseName)
            : this()
        {
            try
            {
                _DataFile = SQLExpressDataFile;
                _databaseName = DatabaseName;
                _structdataSourceProvider = DBProvider.SQLExpress;
                this.PrepareConnectionString();
            }
            catch (Exception err)
            {
                GlobalLog.Logger.LogError(err.Message);
            }
        }

        public PIMManager(DBProvider provider, string connectionString)
            : this()
		{
			try
			{
				_structdataSourceProvider = provider;
				_connectionString = connectionString;
			}
			catch(Exception err)
			{
				GlobalLog.Logger.LogError(err.Message);
			}
		}

		#endregion Constructors
		
		#region Helper Methods
		private string PrepareConnectionString()
		{
			try
			{  
				switch (_structdataSourceProvider)
				{
					case DBProvider.MSAccess:
					case DBProvider.Progress: 
					{
						if (_connectionString == string.Empty)
						{
							if(_structdataSourceProvider == DBProvider.MSAccess)
							{
								_connectionString = "DSN=" + _dataSourceName + ";PWD="+_password+";";
							}
							else
							{
								_connectionString = "DSN=" + _dataSourceName + ";DatabaseName="+_dataSourceName +" ;UID="+_userID+";PWD="+_password+";";
							}
						}						
						break;
					}

                    case DBProvider.ODBC:
                    {                         
                        _connectionString = _dataSourceName.Split(':')[1];
                        break;
                    }

					case DBProvider.SQLServer: 
					{
						if (_connectionString == string.Empty)
						{
							_connectionString = "Server=" + _serverName + ";Database=" + _dsnPointedDatabaseName + ";Trusted_Connection=no; UID="+_userID+";PWD="+_password+";";						
						}
						break;
					}
                    case DBProvider.SQLExpress:
                    {
                        if (_connectionString == string.Empty)
                        {
                            _connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename='" + _dsnPointedDatabaseName + "'" + ";Initial Catalog=" + _databaseName;
                            //_connectionString = @"Data Source=.\SQLEXPRESS; Integrated Security=True;AttachDbFilename=" + _dsnPointedDatabaseName + ";Initial Catalog=" + _databaseName + ";";
                            _structdataSourceProvider = DBProvider.SQLServer;
                        }
                        break;
                    }
					case DBProvider.OLEDB:
					{
						if (_connectionString == string.Empty)
						{
							_connectionString="Server=" + _serverName + ";Database=" + _dsnPointedDatabaseName + ";UID=" + _userID + ";PWD=" + _password + ";";
						}						
						break;
					}
					case DBProvider.Oracle:
					{
						if (_connectionString == string.Empty)
						{
							_connectionString ="Data Source= " + _databaseName  + ";User ID=" + _userID + ";Password=" + _password +";";
						}						
						break;
					}
				}				
			}
			catch (Exception Ex)
			{
				GlobalLog.Logger.LogError(Ex.Message);
			}

			return(_connectionString);
		}


		private string ReplaceDboInQuery(string Query)
		{
			string result = Query;

            if (_structdataSourceProvider != DBProvider.SQLServer)
            {
                result = Query.Replace("dbo.", string.Empty);
            }
            else
            {   
                /* 
                 * @during: SFO
                 * @date: 25/March/2009
                 * dbo.QSISNULL in MSSQL is returing sql_variant, which cannot be used in 
                 * aggregate functions. because of that we had to have to use ISNULL instead of
                 * QSISNULL in such queries. This results in separate queries for SQL and Oracle.
                 * To avoid that, here we are replacing dbo.qsisnull with ISNULL.
                 */

                result = Regex.Replace(Query, "dbo.qsisnull", "ISNULL", RegexOptions.IgnoreCase);
            }

			return(result);
		}


		public IDbDataAdapter GetDataAdapter()
		{
			IDbDataAdapter objDataAdapter = null;

			#region Switch
			switch (_structdataSourceProvider)
			{
				case DBProvider.MSAccess:
				case DBProvider.Progress: 
				case DBProvider.ODBC:
				{						
					objDataAdapter = new OdbcDataAdapter();						
					break;
				}
				case DBProvider.SQLServer: 
				{						
					objDataAdapter = new SqlDataAdapter();						
					break;
				}
				case DBProvider.OLEDB:
				{						
					objDataAdapter =new OleDbDataAdapter ();						
					break;
				}
				case DBProvider.Oracle:
				{
					objDataAdapter = QSOracleServant.GetDataAdapter();
					break;
				}
			}
			#endregion

			return(objDataAdapter);
		}

        public IDbDataAdapter GetNewDataAdapter(string Query)
        {
            IDbDataAdapter Adapter = null;
            if (_structdataSourceProvider == DBProvider.SQLServer)
                Adapter = new SqlDataAdapter();
            else if (_structdataSourceProvider == DBProvider.Oracle)
                Adapter = GetNewOracleDataAdapter();
            _objCommand.CommandText = Query;
            Adapter.SelectCommand = _objCommand;
            return Adapter;
        }

        public int InsertDataSet(string selectQuery, DataSet dataSet)
        {
            int retval = 0;
            dataSet.AcceptChanges(); //Forcing all row states to be 'Unchanged'

            for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                dataSet.Tables[0].Rows[i].SetAdded(); //Forcing row state to be 'Added'
            switch(_structdataSourceProvider)
            {
                case DBProvider.MSAccess:
				case DBProvider.Progress: 
				case DBProvider.ODBC:
				{

                    OdbcDataAdapter ODBC_ADAPTER = (OdbcDataAdapter)GetDataAdapter();
                    _objCommand.CommandText = selectQuery;
                    ODBC_ADAPTER.SelectCommand = (OdbcCommand)_objCommand;
                    OdbcCommandBuilder ODBC_CMDBUILDER = new OdbcCommandBuilder(ODBC_ADAPTER);
                    ODBC_ADAPTER.InsertCommand = ODBC_CMDBUILDER.GetInsertCommand();
                    retval = ODBC_ADAPTER.Update(dataSet);
					break;
				}
				case DBProvider.SQLServer: 
				{						
					SqlDataAdapter SQL_ADAPTER = (SqlDataAdapter)GetDataAdapter();
                    _objCommand.CommandText = selectQuery;
                    SQL_ADAPTER.SelectCommand = (SqlCommand)_objCommand;
                    SqlCommandBuilder SQL_CMDBUILDER = new SqlCommandBuilder(SQL_ADAPTER);
                    SQL_ADAPTER.InsertCommand = SQL_CMDBUILDER.GetInsertCommand();
                    retval = SQL_ADAPTER.Update(dataSet);
                    break;
				}
				case DBProvider.OLEDB:
				{
                    OleDbDataAdapter OLE_ADAPTER = (OleDbDataAdapter)GetDataAdapter();
                    _objCommand.CommandText = selectQuery;
                    OLE_ADAPTER.SelectCommand = (OleDbCommand)_objCommand;
                    OleDbCommandBuilder OLE_CMDBUILDER = new OleDbCommandBuilder(OLE_ADAPTER);
                    OLE_ADAPTER.InsertCommand = OLE_CMDBUILDER.GetInsertCommand();
                    retval = OLE_ADAPTER.Update(dataSet);
					break;
				}
				case DBProvider.Oracle:
				{
                    retval = OracleBulkUpdate(selectQuery, dataSet);
					break;
				}
            }
            return retval;           
        }
        private int OracleBulkUpdate(string selectQuery, DataSet dataSet)
        {
            OracleDataAdapter ORACLE_ADAPTER = (OracleDataAdapter)GetDataAdapter();
            _objCommand.CommandText = selectQuery;
            ORACLE_ADAPTER.SelectCommand = (OracleCommand)_objCommand;
            OracleCommandBuilder ORACLE_CMDBUILDER = new OracleCommandBuilder(ORACLE_ADAPTER);
            ORACLE_ADAPTER.InsertCommand = ORACLE_CMDBUILDER.GetInsertCommand();
            return ORACLE_ADAPTER.Update(dataSet);
        }
        public IDbDataAdapter GetNewOracleDataAdapter()
        {
            return new OracleDataAdapter();
        }

		private void PrepareParameters(Hashtable hsInputParameterValues,ref Hashtable hsOutputParameterValues)
		{
			IDbDataParameter objParam = null;
			IDbDataParameter objOutParam = null;
			IDbDataParameter objRetParam = null;

			//Clearing the parameters
			_objCommand.Parameters.Clear();

			#region Switch
			switch (_structdataSourceProvider)
			{
				case DBProvider.MSAccess:
				case DBProvider.Progress: 
				case DBProvider.ODBC:
				{						
					foreach (DictionaryEntry objDictionaryEnum in hsInputParameterValues)
					{		
						objParam = new OdbcParameter("@" + objDictionaryEnum.Key, objDictionaryEnum.Value);
						_objCommand.Parameters.Add(objParam);
					}					
					break;
				}
				case DBProvider.SQLServer: 
				{						
					foreach (DictionaryEntry objDictionaryEnum in hsInputParameterValues)
					{
						objParam = new SqlParameter( "@" + objDictionaryEnum.Key , objDictionaryEnum.Value );
						_objCommand.Parameters.Add(objParam);
					}

					foreach (DictionaryEntry objDictionaryEnum in hsOutputParameterValues)
					{
						objOutParam = new SqlParameter( "@" + objDictionaryEnum.Key ,(SqlDbType)GetDBType( objDictionaryEnum.Value ) , GetMaxSize( objDictionaryEnum.Value ), ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Current, null); 
						_objCommand.Parameters.Add(objOutParam);
					}
						
					objRetParam = new SqlParameter( "" , SqlDbType.VarChar , 1000, ParameterDirection.ReturnValue, false, 0, 0, null, DataRowVersion.Current, null); 
					_objCommand.Parameters.Add(objRetParam);
					
					break;
				}
				case DBProvider.OLEDB:
				{
					//Looping through the Input Parameters
					foreach (DictionaryEntry objDictionaryEnum in hsInputParameterValues)
					{
						objParam = new OleDbParameter(objDictionaryEnum.Key.ToString(), objDictionaryEnum.Value);
						_objCommand.Parameters.Add(objParam);
					}

					//Looping through the Output Parameters
					foreach (DictionaryEntry objDictionaryEnum in hsOutputParameterValues)
					{
						objOutParam = new OleDbParameter (objDictionaryEnum.Key.ToString(),(OleDbType) GetDBType (objDictionaryEnum.Value ),GetMaxSize(objDictionaryEnum.Value),ParameterDirection.Output ,false,0,0,null,DataRowVersion.Current ,null);
						_objCommand.Parameters.Add(objOutParam);
					}

					objRetParam = new OleDbParameter ("" ,OleDbType.VarChar, 1000 ,ParameterDirection.ReturnValue ,false,0,0,null,DataRowVersion.Current ,null); 
					_objCommand.Parameters.Add(objRetParam);					
					break;
				}
				case DBProvider.Oracle:
				{
					QSOracleServant.PrepareParameters(ref _objCommand,ref refCursorTable, hsInputParameterValues, ref hsOutputParameterValues);					
					//OracleParameter returnParam = new OracleParameter("" ,OracleDbType.Number , 1000 ,ParameterDirection.ReturnValue ,false,0,0,null,DataRowVersion.Current ,null);
					//_oracleCommand.Parameters.Add(returnParam);
					break;
				}
			}
			#endregion
		}


		private void DeriveParameterValues(ref Hashtable hsOutputParameterValues, ref object ReturnValue)
		{
			if(_structdataSourceProvider == DBProvider.Oracle || 
				_structdataSourceProvider == DBProvider.SQLServer ||
				_structdataSourceProvider == DBProvider.OLEDB
				)
			{
				for(int i = 0; i < _objCommand.Parameters.Count; i++)
				{
					IDbDataParameter objResParam = (IDbDataParameter)_objCommand.Parameters[i];
					string ParameterName = objResParam.ParameterName.Replace("@","");

					if(objResParam.Direction == ParameterDirection.Output)
					{
						hsOutputParameterValues[ParameterName] = objResParam.Value;
					}

					if(objResParam.Direction == ParameterDirection.ReturnValue)
					{
						ReturnValue = objResParam.Value;
					}
				}
			}

			_objCommand.Parameters.Clear();
		}


		public string ConvertToDBDate(string date)
		{
			return QSecureDBHelper.ConvertToDBDate(date, _structdataSourceProvider);
		}

		
		#endregion Helper Methods

		#region Methods

        private int GetTimeOut(string ConfigName)
        {
            int result = string.Compare(ConfigName, "DBCOMMANDTIMEOUT", true) == 0 ? COMMAND_TIMEOUT : 0;

            QSecureConfig config = new QSecureConfig();
            try
            {
                string val = config.GetValue(ConfigName);

                if (!string.IsNullOrEmpty(val))
                {
                    result = Convert.ToInt32(val);
                }
            }
            catch
            { }

            return (result);
        }

        private DBProvider GetProvider(string DataSourceName)
		{
			try
			{
                if (DataSourceName.StartsWith("ODBC", StringComparison.InvariantCultureIgnoreCase))
                {
                    _dataSourceProvider = "ODBC";
                    return DBProvider.ODBC;
                
                }

                _serverName = QSecureRegistry.GetStringValue(_PROVIDERHIVE + "\\" + DataSourceName, "Server");
                _dataSourceProvider = QSecureRegistry.GetStringValue(_PROVIDERHIVE + "\\" + "ODBC Data Sources", DataSourceName);
                

				switch(_dataSourceProvider)
				{
					case "Microsoft Access Driver (*.mdb)":
					{
						return DBProvider.MSAccess;   
					}
					case "Driver do Microsoft Access (*.mdb)":
					{
						return DBProvider.MSAccess;   
					}
					case "SQL Server":
					{
						_dsnPointedDatabaseName = QSecureRegistry.GetStringValue(_PROVIDERHIVE + "\\" +DataSourceName, "DataBase"); 
						return DBProvider.SQLServer;   
					}
					case "MERANT 3.60 32-BIT Progress SQL92 v9.1D":
					{
						//_dsnPointedDatabaseName = objRegistry.QueryStringValue(DataSourceName,"DatabaseName"); 
						return DBProvider.Progress;   
					}
					case "ODBC for Oracle":
					{
						return DBProvider.OLEDB; 
					}
					case "Oracle in OraClient10g_home":
					{
						return DBProvider.Oracle;   
					}
					default:
					{
						return DBProvider.Null ;   
					}
				}
			}
			catch(Exception	err)
			{
				GlobalLog.Logger.LogError(err.Message);
				return DBProvider.Null;   
			}
		}

		private void ClearTrans()
		{			
			if(_objTrans != null)
			{
				_objTrans.Dispose();
			}
			_objTrans = null;
			TransON = false;
			
			if(_objCommand !=null)
			{
				_objCommand.Dispose();
			}
			_objCommand = null;

			if(_objConnection != null) 
			{
				if(_objConnection.State == ConnectionState.Open) 
				{
					_objConnection.Close();
				}
				_objConnection.Dispose();
			}
			_objConnection = null;
		}

		/// <summary>
		/// Use this function before using the QSecureManager object and remember to call CommitTrans() or RollBackTrans() before dispose this object
		/// </summary>
	
		public bool BeginTrans()
		{
			try
			{  				
				this.Open();
				_objTrans = _objConnection.BeginTransaction();
				_objCommand.Transaction = _objTrans;
				return(TransON = true);
			}
			catch (Exception Ex)
			{
				GlobalLog.Logger.LogError(Ex.Message);
				return TransON=false;
			}
		}

		private void SetDateFormat()
		{
			if ( _structdataSourceProvider == DBProvider.Oracle )
			{
				Execute("ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH:MI:SS AM'");
			}
		}

		public bool Open()
		{
			try
			{  	
				if(ConnectionStatus == ConnectionState.Open)
				{
					return (true);
				}

				switch (_structdataSourceProvider)
				{
					case DBProvider.MSAccess:
					case DBProvider.Progress: 
					case DBProvider.ODBC:
					{
						if(_objConnection == null)
						{
							_objConnection = new OdbcConnection(_connectionString);
						}
						break;
					}
					case DBProvider.SQLServer: 
					{						
						if(_objConnection == null) 
						{
							//If Application is not already found in the connection string set it
							//Note that because of this logic, application name can be set only once
							//in the connection string for an instance of the DBManager object
                            if (_connectionString.IndexOf("SQLEXPRESS") == -1)
                            {
							    if (_connectionString.IndexOf("Application Name") == -1)
								    _connectionString += ";Application Name=" + ApplicationName;

                                if ((_connectionString.IndexOf("Connection Timeout") == -1) && (_dbConnectionTimeout > 0))
                                    _connectionString += ";Connection Timeout=" + _dbConnectionTimeout;
                                
							    if (_connectionString.IndexOf("Pooling") == -1)
								    _connectionString += ";Pooling=" + _enableConnectionPooling;
                            }
							_objConnection = new SqlConnection(_connectionString);
						}

						break;
					}
					case DBProvider.OLEDB:
					{						
						if(_objConnection == null) 
						{
							_objConnection = new OleDbConnection (_connectionString);
						}

						break;
					}
					case DBProvider.Oracle:
					{												
						if (_objConnection == null)
						{
                            if ((_connectionString.IndexOf("Connection Timeout") == -1) && (_dbConnectionTimeout > 0))
                                _connectionString += ";Connection Timeout=" + _dbConnectionTimeout;

							_objConnection = QSOracleServant.GetConnection(_connectionString);
						}						
						break;
					}
				}

				if(_objConnection.State == ConnectionState.Closed)
				{
                    try
                    {
                        _objConnection.Open();
                    }
                    catch(Exception Ex)
                    {
                        GlobalLog.Logger.LogError(Ex.Message);
                        ClearPool();
                        _objConnection.Open();
                    }

				}

				_objCommand = _objConnection.CreateCommand();
				_objCommand.Connection = _objConnection;
				//TransON = true;
				SetDateFormat();
                SetSessionContext();
				return true;
			}
			catch (Exception Ex){GlobalLog.Logger.LogError(Ex.Message); TransON=false;return false;}
		}

        public void ClearPool()
        {
            try
            {
                switch (_structdataSourceProvider)
                {
                    case DBProvider.SQLServer:
                        {
                            SqlConnection.ClearPool((SqlConnection)_objConnection);
                            break;
                        }
                    case DBProvider.Oracle:
                        {
                            QSOracleServant.ClearPool(_objConnection);
                            break;
                        }
                }
            }
            catch (Exception)
            {
             //   ex = ex;
            }
        }

		/// <summary>
		/// This function is used when all transactions completed sucessfully, if Error comes Call RollBackTrans()
		/// </summary>
		public void CommitTrans()
		{
			if(_objTrans != null)
			{
				if(_objTrans.Connection.State == ConnectionState.Open)
				{
					_objTrans.Commit();
				}
			}

			ClearTrans();
		}


		/// <summary>
		/// This function is used when all transactions completed sucessfully, if Error comes Call RollBackTrans()
		/// </summary>
		public void Close()
		{
            try
            {
                ClearTrans();
            }
            catch (Exception Ex)
            {
                GlobalLog.Logger.LogError(Ex.Message);
            }

		}


		

		/// <summary>
		///  This function is used when transactions came with an Error, if No Error then Call CommitTrans()
		/// </summary>
		public void RollBackTrans()
		{
			if(_objTrans != null)
			{
				if(_objTrans.Connection.State == ConnectionState.Open)
				{
					_objTrans.Rollback();
				}
			}

			ClearTrans();
		}


		/// <summary>
		/// This Function Can be used to Modify the Database using UPDATE, INSERT or DELETE Queries, [must be called between BeginTrans() and CommitTrans() functions]
		/// </summary>
		/// <param name="Query">input Query</param>
		/// <returns>returns updated rows count</returns>
		public int Execute(string Query)
		{
            StartDBTrace();
            
			//if(!TransON)throw new Exception("WARNING!! You have not used the function BeginTrans() or you used a function CommitTrans()."); 			
			int RetVal = 0;
            try
            {
                Monitor.Enter(this);
                _objCommand.CommandText = ReplaceDboInQuery(Query);
                _objCommand.CommandType = CommandType.Text;

                _objCommand.CommandTimeout = _dbCommandTimeout;
                RetVal = _objCommand.ExecuteNonQuery();
                return RetVal;
            }
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
			finally
			{
			    EndDBTrace(Query, null);
                Monitor.Exit(this);
			}
			
		}

        public string GetParameterValues(Hashtable parameterHash)
        {
            StringBuilder parameters = new StringBuilder();
           
            foreach (DictionaryEntry objDictionaryEnum in parameterHash)
            {
                if (objDictionaryEnum.Value.GetType().Name != "Byte[]")
                {
                    parameters.AppendFormat("{0}={1}&", objDictionaryEnum.Key, objDictionaryEnum.Value);
                }
            }
            
            if (parameters.Length>0) parameters.Length -= 1;

            if (parameters.Length > 2000) parameters.Length = 2000;

            return parameters.ToString();
        }
      
        public void StartDBTrace()
        {
            if (!_SQLTraceOn)
                return;
            dtTraceStartDate = DateTime.Now;
            _lastException = string.Empty;
        }

        public void EndDBTrace(string query, Hashtable paramObject)
        {
            if (!_SQLTraceOn)
                return;
            
            int elapsedTime = DateTime.Now.Subtract(dtTraceStartDate).Milliseconds;

            string finalQuery = (DBPROVIDER == DBProvider.Oracle) ? INS_PROFILE_LOG_ORA : INS_PROFILE_LOG_SQL;
            string parameters = "";
            string previousCommand = "";

            if (paramObject != null)
                parameters = GetParameterValues(paramObject);

            Hashtable hsParameters = new Hashtable();
            hsParameters.Add("TIME", DateTime.Now.ToString());
            hsParameters.Add("APPLICATION", ApplicationName);
            hsParameters.Add("QUERY", query);
            hsParameters.Add("PARAMETER", parameters);
            hsParameters.Add("ELPASEDTIME", elapsedTime.ToString());
            hsParameters.Add("CONTEXT", Context);
            hsParameters.Add("EXCEPTION", _lastException.Length>200?_lastException.Substring(0,200):_lastException);
            hsParameters.Add("STATUS", (_lastException==string.Empty)?1:0);
            hsParameters.Add("PROCESSID", Process.GetCurrentProcess().Id);
            hsParameters.Add("PROCESSNAME", Process.GetCurrentProcess().ProcessName);
            hsParameters.Add("MACHINENAME", Environment.MachineName);
            hsParameters.Add("INFO1", _TraceValue);

            try
            {
                Monitor.Enter(this);
                _SQLTraceOn = false;
                previousCommand = GetLastExecQuery();
                Execute(finalQuery, hsParameters);
            }
            catch (Exception e)
            {
                string msg = e.Message;
            }
            finally
            {
                _SQLTraceOn = true;
                _objCommand.CommandText = previousCommand;
                Monitor.Exit(this);
            }
                    
        }

		/// <summary>
		/// 
		/// </summary>
		/// <param name="ProcedureName"></param>
		/// <param name="hsInputOutputParameterValues"></param>
		/// <returns></returns>
		public int ExecuteStoreProcedure(string ProcedureName ,Hashtable hsInputOutputParameterValues)
		{	
			int RetVal = 0;
			IDbDataParameter objParam = null;
            StartDBTrace();
            
			try
			{
				Monitor.Enter(this);	
				_objCommand.Parameters.Clear();
				_objCommand.CommandType = CommandType.StoredProcedure;
				_objCommand.CommandText = ProcedureName;

				#region Switch
				switch (_structdataSourceProvider)
				{
					case DBProvider.SQLServer :
					{					
						foreach (DictionaryEntry objDictionaryEnum in hsInputOutputParameterValues)
						{							
							objParam = new  SqlParameter("@" +  objDictionaryEnum.Key , objDictionaryEnum.Value );
							_objCommand.Parameters.Add(objParam);
						}												
						break;
					}
					case DBProvider.OLEDB:
					{												
						foreach (DictionaryEntry objDictionaryEnum in hsInputOutputParameterValues)
						{
							objParam = new OleDbParameter (objDictionaryEnum.Key.ToString (),objDictionaryEnum.Value );
							_objCommand.Parameters.Add (objParam);
						}											
						break;
					}
					case DBProvider.Oracle:
					{	
						QSOracleServant.PrepareParamCollection(hsInputOutputParameterValues, ref _objCommand);
						break;
					}					
				}
				#endregion

				RetVal = _objCommand.ExecuteNonQuery();
                return RetVal;
			}
			catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
			finally
			{
			    EndDBTrace(ProcedureName, hsInputOutputParameterValues);
                Monitor.Exit(this);
			}
		}

		/// <summary>
		/// This function is used when we need to insert Binary Datatype in the Table
		/// </summary>
		/// <param name="Query">The query format like     INSERT INTO STUDIO(ID, NAME, PHOTO) VALUES (23 ,'FOX VIVA', ?)    where the question will be replaced with the binary data, this data will be passed as hash table </param>
		/// <param name="hsBlobValues">This hash table key will be the field name and the value will be the binary data say 'byte[]'</param>
		/// <returns>return updated rows count</returns>
		public int Execute(string Query , Hashtable hsBlobValues)
		{
			//if(!TransON)throw new Exception("WARNING!! You have not used the function BeginTrans() or you used a function CommitTrans()."); 

			int RetVal = 0;
			IDbDataParameter objParam = null;
            StartDBTrace();

            try
			{
                Monitor.Enter(this);
				_objCommand.Parameters.Clear();
				_objCommand.CommandText = ReplaceDboInQuery(Query);
				_objCommand.CommandType=CommandType.Text;  

				switch (_structdataSourceProvider)
				{
					case DBProvider.MSAccess:
					case DBProvider.Progress: 
					case DBProvider.ODBC:
					{	
						foreach (DictionaryEntry objDictionaryEnum in hsBlobValues)
						{
                            if (objDictionaryEnum.Value.GetType().Name == "Byte[]")
                                objParam = new  OdbcParameter("@" +  objDictionaryEnum.Key  , OdbcType.Image, ((byte[]) objDictionaryEnum.Value).Length   , ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value );
                            else
                                objParam = new OdbcParameter("@" + objDictionaryEnum.Key, (OdbcType)GetDBType(objDictionaryEnum.Value), objDictionaryEnum.Value.ToString().Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value);
							_objCommand.Parameters.Add(objParam);
						}						
						break;
					}
					case DBProvider.SQLServer: 
					{
						foreach (DictionaryEntry objDictionaryEnum in hsBlobValues)
						{
							if ( objDictionaryEnum.Value.GetType().Name == "Byte[]" )
								objParam = new  SqlParameter("@" +  objDictionaryEnum.Key , SqlDbType.Image , ((byte[]) objDictionaryEnum.Value).Length   , ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value );
							else
								objParam = new  SqlParameter("@" +  objDictionaryEnum.Key , (SqlDbType)GetDBType( objDictionaryEnum.Value ) , objDictionaryEnum.Value.ToString().Length   , ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value );
							_objCommand.Parameters.Add(objParam);
						}						
						break;
					}
					case DBProvider.OLEDB:
					{
						foreach(DictionaryEntry objDictionaryEnum in hsBlobValues)
						{
							if ( objDictionaryEnum.Value.GetType().Name == "Byte[]" )
								objParam = new OleDbParameter(objDictionaryEnum.Key.ToString (),OleDbType.Binary,((byte[]) objDictionaryEnum.Value).Length ,ParameterDirection.Input ,false,0,0,null,DataRowVersion.Current ,objDictionaryEnum.Value );
							else
								objParam = new OleDbParameter(objDictionaryEnum.Key.ToString (),OleDbType.LongVarChar,objDictionaryEnum.Value.ToString().Length ,ParameterDirection.Input ,false,0,0,null,DataRowVersion.Current ,objDictionaryEnum.Value );

							_objCommand.Parameters.Add(objParam);
						}
						break;
					}
					case DBProvider.Oracle:
					{	
						QSOracleServant.PrepareBlobParameters(ref _objCommand, hsBlobValues);						
						break;
					}
				}

				RetVal = _objCommand.ExecuteNonQuery ();
				_objCommand.Parameters.Clear();
                return RetVal;
			}
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
			finally
			{
			    EndDBTrace(Query, hsBlobValues);
                Monitor.Exit(this);
			}
		}

        /// <summary>
        /// This method allows the execution of parameterized queries.
        /// e.g.,
        ///        string query = "SELECT QSID, MANAGERID, FIRSTNAME FROM PERSons WHERE (LASTNAME=@FN AND EMAIL=@EM) OR QSID>@QSID";
        ///        Hashtable param = new Hashtable();
        ///        param.Add("QSID", 665);
        ///        param.Add("FN", "man");
        ///        param.Add("EM", "m.singh@bt.com");
        ///        DataTable dt = dbMgr.ExecuteQuery(query, param);
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string query, Hashtable parameters)
        {
            DataSet ds = new DataSet();
            StartDBTrace();

            try
            {
                lock (this)
                {

                    _objCommand.Parameters.Clear();
                    _objCommand.CommandText = ReplaceDboInQuery(query);
                    _objCommand.CommandType = CommandType.Text;

                    PrepareParameters(parameters);

                    IDbDataAdapter objDataAdapter = GetDataAdapter();
                    objDataAdapter.SelectCommand = _objCommand;
                    objDataAdapter.Fill(ds);
                    _objCommand.Parameters.Clear();
                    return ds.Tables[0];
                }
            }
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
            finally
            {
                EndDBTrace(query, parameters);
            }
        }

        /// <summary>
        /// ODBC version of ExecuteQuery() method
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string query, ArrayList parameters)
        {
            DataSet ds = new DataSet();
            StartDBTrace();

            try
            {
                lock (this)
                {
                    _objCommand.Parameters.Clear();
                    _objCommand.CommandText = ReplaceDboInQuery(query);
                    _objCommand.CommandType = CommandType.Text;

                    PrepareParameters(parameters);

                    IDbDataAdapter objDataAdapter = GetDataAdapter();
                    objDataAdapter.SelectCommand = _objCommand;
                    objDataAdapter.Fill(ds);
                    _objCommand.Parameters.Clear();

                    return ds.Tables[0];
                }
            }
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
            finally
            {
                EndDBTrace(query, null);
            }
        }

        public IDataReader ExecuteReader(string query, Hashtable parameters)
        {
            IDataReader objReader = null;
            StartDBTrace();

            try
            {
                Monitor.Enter(this);
                _objCommand.Parameters.Clear();
                _objCommand.CommandType = CommandType.Text;
                _objCommand.CommandText = ReplaceDboInQuery(query);
                PrepareParameters(parameters);
                objReader = _objCommand.ExecuteReader();
                _objCommand.Parameters.Clear();
                return (objReader);
            }
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
            finally
            {
                EndDBTrace(query, parameters);
                Monitor.Exit(this);
            }
        }

        /// <summary>
        /// ODBC parameters are positional not name based.
        /// Hence passing the parameters as Hashtable (which doesn't guarantee the position of the elements)
        /// were resulting in errors. To fix that, we have to use this method.
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string query, ArrayList parameters)
        {
            IDataReader objReader = null;
            StartDBTrace();

            try
            {
                Monitor.Enter(this);
                _objCommand.Parameters.Clear();
                _objCommand.CommandType = CommandType.Text;
                _objCommand.CommandText = ReplaceDboInQuery(query);
                PrepareParameters(parameters);
                objReader = _objCommand.ExecuteReader();
                _objCommand.Parameters.Clear();
                return (objReader);
            }
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
            finally
            {
                EndDBTrace(query, null);
                Monitor.Exit(this);
            }            
        }


        private void PrepareParameters(ArrayList parameters)
        {
            IDbDataParameter objParam = null;

            //Clearing the parameters
            _objCommand.Parameters.Clear();

            switch (_structdataSourceProvider)
            {
                case DBProvider.MSAccess:
                case DBProvider.Progress:
                case DBProvider.ODBC:
                    {
                        
                        for(int i=0; i< parameters.Count; ++i)
                        {
                            object data = parameters[i];
                            if (data != null && data.GetType().Name == "Byte[]")
                                objParam = new OdbcParameter(String.Format("p{0}", i), OdbcType.Image, ((byte[])data).Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, data);
                            else
                                objParam = new OdbcParameter(String.Format("p{0}", i), (OdbcType)GetDBType(data),
                                    data.ToString().Length,
                                    ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, data);
                            _objCommand.Parameters.Add(objParam);
                        }
                        break;
                    }
                default:
                    throw new ApplicationException(" Only supported for ODBC connections!");
            }
        }

        private void PrepareParameters(Hashtable parameters)
        {
            IDbDataParameter objParam = null;

            //Clearing the parameters
            _objCommand.Parameters.Clear();

            switch (_structdataSourceProvider)
            {
                case DBProvider.MSAccess:
                case DBProvider.Progress:
                case DBProvider.ODBC:
                    {
                        foreach (DictionaryEntry objDictionaryEnum in parameters)
                        {
                            if (objDictionaryEnum.Value.GetType().Name == "Byte[]")
                                objParam = new OdbcParameter("@" + objDictionaryEnum.Key, OdbcType.Image, ((byte[])objDictionaryEnum.Value).Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value);
                            else
                                objParam = new OdbcParameter(objDictionaryEnum.Key.ToString(), (OdbcType)GetDBType(objDictionaryEnum.Value),
                                    objDictionaryEnum.Value.ToString().Length, 
                                    ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value);
                            _objCommand.Parameters.Add(objParam);
                        }
                        break;
                    }
                case DBProvider.SQLServer:
                    {
                        foreach (DictionaryEntry objDictionaryEnum in parameters)
                        {
                            if (objDictionaryEnum.Value.GetType().Name == "Byte[]")
                                objParam = new SqlParameter("@" + objDictionaryEnum.Key, SqlDbType.Image, ((byte[])objDictionaryEnum.Value).Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value);
                            else
                                objParam = new SqlParameter("@" + objDictionaryEnum.Key, (SqlDbType)GetDBType(objDictionaryEnum.Value), objDictionaryEnum.Value.ToString().Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value);
                            _objCommand.Parameters.Add(objParam);
                        }
                        break;
                    }
                case DBProvider.OLEDB:
                    {
                        foreach (DictionaryEntry objDictionaryEnum in parameters)
                        {
                            if (objDictionaryEnum.Value.GetType().Name == "Byte[]")
                                objParam = new OleDbParameter(objDictionaryEnum.Key.ToString(), OleDbType.Binary, ((byte[])objDictionaryEnum.Value).Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value);
                            else
                                objParam = new OleDbParameter(objDictionaryEnum.Key.ToString(), OleDbType.LongVarChar, objDictionaryEnum.Value.ToString().Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, objDictionaryEnum.Value);

                            _objCommand.Parameters.Add(objParam);
                        }
                        break;
                    }
                case DBProvider.Oracle:
                    {
                        QSOracleServant.PrepareBlobParameters(ref _objCommand, parameters);
                        break;
                    }
            }
        }
		
		/// <summary>
		/// This function is used when we need to Call StoredProcedures
		/// </summary>
		/// <param name="ProcedureName">The Stored Procedure format like sp_GetRecords</param>
		/// <param name="hsInputParameterValues">This hash table key will be the field name and the value will be the binary data say 'byte[]'</param>
		/// <returns>return updated rows count</returns>
		public int Execute(string ProcedureName , Hashtable hsInputParameterValues, ref object ReturnValue)
		{
			Hashtable hsOutputParameterValues = new Hashtable();
			return Execute(ProcedureName, hsInputParameterValues, ref hsOutputParameterValues, ref ReturnValue);
		}

		/// <summary>
		/// This function is used when we need to Call StoredProcedures
		/// </summary>
		/// <param name="ProcedureName">The Stored Procedure format like sp_GetRecords</param>
		/// <param name="hsInputParameterValues">This hash table key will be the field name and the value will be the binary data say 'byte[]'</param>
        /// <param name="hsOutputParameterValues">This hash table key will be the field name and the value will be the binary data say 'byte[]'</param>
		/// <returns>return updated rows count</returns>
		public int Execute(string ProcedureName , Hashtable hsInputParameterValues, ref Hashtable hsOutputParameterValues, ref object ReturnValue)
		{
			//if(!TransON)throw new Exception("WARNING!! You have not used the function BeginTrans() or you used a function CommitTrans()."); 

			int RetVal = 0;
            StartDBTrace();

			try
			{
				Monitor.Enter(this);				
				_objCommand.CommandType = CommandType.StoredProcedure;				
				_objCommand.CommandText = ProcedureName;
                _objCommand.CommandTimeout = _dbCommandTimeout;
				this.PrepareParameters(hsInputParameterValues,ref hsOutputParameterValues);
				RetVal = _objCommand.ExecuteNonQuery();
				this.DeriveParameterValues(ref hsOutputParameterValues, ref ReturnValue);
                return RetVal;
			}
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
			finally
			{
                EndDBTrace(ProcedureName, hsInputParameterValues);
				Monitor.Exit(this);
			}			
		}

		private object GetDBType(object objvalue)
		{
			object ret = null;
			switch(_structdataSourceProvider)
			{
				case DBProvider.MSAccess:
				case DBProvider.Progress:
				case DBProvider.ODBC:
				{
					switch(objvalue.GetType().Name  )
					{
						case "Int64": {ret = OdbcType.BigInt ; break;}
						case "Int32": {ret = OdbcType.Int; break;}
						case "Int16": {ret = OdbcType.SmallInt ; break; }
						case "String":{ret = OdbcType.VarChar ; break; }
						case "DateTime": {ret = OdbcType.DateTime ; break;}
						case "Boolean": {ret = OdbcType.Bit ; break; }
						case "Byte[]": {ret = OdbcType.Binary ; break;}
						default: {ret = OdbcType.VarChar ; break; }
					}
					break;
				}
				case DBProvider.SQLServer:
				{
					switch(objvalue.GetType().Name)
					{
						case "Int64": {ret = SqlDbType.BigInt; break;}
						case "Int32": {ret = SqlDbType.Int;	break;}
						case "Int16": {ret = SqlDbType.SmallInt; break;}
						case "String": {ret = SqlDbType.VarChar; break;}
						case "DateTime": {ret = SqlDbType.DateTime; break;}
						case "Boolean": {ret = SqlDbType.Bit; break;}
						case "Byte[]": {ret = SqlDbType.Binary; break;}
						case "NTextString" : {ret = SqlDbType.NText; break;}
						default: {ret = SqlDbType.VarChar; break;}
					}
					break;
				}
				case DBProvider.OLEDB:
				{
					switch(objvalue.GetType ().Name)
					{
						case "Int64": {ret = OleDbType.BigInt; break;}
						case "Int32": {ret = OleDbType.Integer; break;}
						case "Int16": {ret = OleDbType.SmallInt; break;}
						case "String": {ret = OleDbType.VarChar; break;}
						case "DateTime": {ret = OleDbType.DBTimeStamp ; break;}
						case "Boolean": {ret = OleDbType.Boolean; break; }
						case "Byte[]": {ret = OleDbType.Binary;break; }
						default: {ret = OleDbType.VarChar ; break; }
					}
					break;
				}
				case DBProvider.Oracle:
				{
					ret = QSOracleServant.GetDBType(objvalue);					
					break;
				}
			}
			return ret;
		}
		
		/// <summary>
		/// 
		/// </summary>
		/// <param name="objvalue"></param>
		/// <returns></returns>
		private int GetMaxSize(object objvalue)
		{
			int ret = int.MaxValue;
			switch(objvalue.GetType().Name)
			{
				case "Int64": {ret = int.MaxValue; break;}
				case "Int32": {ret = int.MaxValue;	break;}
				case "Int16": {ret = short.MaxValue; break;}
				case "String": {ret = 8000; break;}
				case "DateTime": {ret = objvalue.ToString().Length; break;}
				case "Boolean": {ret = 10; break;}
				case "Byte[]": {ret =  ((byte[]) objvalue).Length ; break;}
				default: {ret = 8000; break;}
			}

			return ret;
		}

		/// <summary>
		/// This Function Will Return Maximum plus One value of a Table and Column Specified
		/// </summary>
		/// <param name="TableName">Table name toGet the Max</param>
		/// <param name="FieldName">Column Name to Get the Max</param>
		/// <returns>Integer</returns>
		
		
		public long GetNewID(string TableName, string FieldName)
		{
			//if(!TransON)throw new Exception("WARNING!! You have not used the function BeginTrans() or you used a function CommitTrans()."); 			
			long RetVal = 0;

			RetVal = (long) this.GetValue(string.Format(GET_NEW_ID,FieldName,TableName));

			return RetVal;
		}


		/// <summary>
		/// This Function Will return Object of single value of any datatype 
		/// </summary>
		/// <param name="Query">input Query</param>
		/// <returns></returns>
		public object GetValue(string Query)
		{
			//if(!TransON)throw new Exception("WARNING!! You have not used the function BeginTrans() or you used a function CommitTrans()."); 
			object RetVal = null;
            StartDBTrace();

            try
			{
				Monitor.Enter(this);
				_objCommand.CommandType = CommandType.Text;
				_objCommand.CommandText = ReplaceDboInQuery(Query);
				RetVal = _objCommand.ExecuteScalar();
                return RetVal;
			}
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
			finally
			{
                EndDBTrace(Query, null);
				Monitor.Exit(this);
			}			
		}
       
        public object GetValue(string Query, Hashtable hsInputParameterValues)
        {
            DataTable objDataTable = GetData(Query, hsInputParameterValues);
            if (objDataTable != null && objDataTable.Rows.Count > 0)
                return objDataTable.Rows[0][0];
            else
                return null;
        }


	    /// <summary>
		/// Returns DataTable if supply the Select Query
		/// </summary>
		/// <param name="Query">input Query</param>
		/// <returns>DataTable</returns>
		public DataTable GetData(string Query)
		{
			//if(!TransON)throw new Exception("WARNING!! You have not used the function BeginTrans() or you used a function CommitTrans()."); 
			DataSet  objDataSet = new DataSet();
			IDbDataAdapter objDataAdapter = null;
            StartDBTrace();

			try
			{
				Monitor.Enter(this);
				_objCommand.CommandType =CommandType.Text; 
				_objCommand.CommandText = ReplaceDboInQuery(Query);
				_objCommand.CommandTimeout = _dbCommandTimeout;
				objDataAdapter = this.GetDataAdapter();
				objDataAdapter.SelectCommand = _objCommand;
				objDataAdapter.Fill(objDataSet);
                if (objDataSet.Tables.Count > 0)
                {
                    DataTable result = objDataSet.Tables[0];
                    objDataSet.Tables.RemoveAt(0);
                    result.TableName = "";
                    return (result);
                }
                else
                {
                    return (null);
                }		
			}
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
			finally
			{
                EndDBTrace(Query, null);
				Monitor.Exit(this);
			}				
		}

        public DataTable GetData(string Query, Hashtable hsInputParameterValues)
        {
            DataSet objDataSet = GetDataSet(Query, hsInputParameterValues);

            if (objDataSet != null && objDataSet.Tables.Count > 0)
            {
                DataTable result = objDataSet.Tables[0];
                objDataSet.Tables.RemoveAt(0);
                result.TableName = "";
                return (result);
            }
            return (null);
        }

		/// <summary>
		/// 
		/// </summary>
		/// <param name="ProcedureName"></param>
		/// <param name="hsInputParameterValues"></param>
		/// <param name="hsOutputParameterValues"></param>
		/// <param name="ReturnValue"></param>
		/// <returns></returns>
		public DataTable GetData(string ProcedureName , Hashtable hsInputParameterValues, ref Hashtable hsOutputParameterValues, ref object ReturnValue)
		{
			DataSet  objDataSet = new DataSet();
            StartDBTrace();

			try
			{
				Monitor.Enter(this);
                _objCommand.CommandType =CommandType.StoredProcedure;    
				_objCommand.CommandText = ProcedureName;
				this.PrepareParameters(hsInputParameterValues,ref hsOutputParameterValues);
				IDbDataAdapter objDataAdapter = GetDataAdapter();
				objDataAdapter.SelectCommand = _objCommand;
				objDataAdapter.Fill(objDataSet);
                
				this.DeriveParameterValues(ref hsOutputParameterValues,ref ReturnValue);

                if (objDataSet.Tables.Count > 0)
                {
                    DataTable result = objDataSet.Tables[0];
                    objDataSet.Tables.RemoveAt(0);
                    result.TableName = "";
                    return (result);
                }
                else
                {
                    return (null);
                }	
			}
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
			finally
			{
                EndDBTrace(ProcedureName, hsInputParameterValues);
				Monitor.Exit(this);
			}
		}


		/// <summary>
		/// Returns DataSet if supply the Select Query
		/// </summary>
		/// <param name="Query">input Query</param>
		/// <returns>DataSet</returns>
		public DataSet GetDataSet(string Query)
		{
			//if(!TransON)throw new Exception("WARNING!! You have not used the function BeginTrans() or you used a function CommitTrans()."); 

			DataSet objDataSet = new DataSet();
            StartDBTrace();

			try
			{
				Monitor.Enter(this);
                _objCommand.CommandType =CommandType.Text; 
				_objCommand.CommandText = ReplaceDboInQuery(Query);
				_objCommand.CommandTimeout = _dbCommandTimeout;
				IDbDataAdapter objDataAdapter = GetDataAdapter();
				objDataAdapter.SelectCommand = _objCommand;
				objDataAdapter.Fill(objDataSet);
                return objDataSet;
			}
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
			finally
			{
                EndDBTrace(Query, null);
				Monitor.Exit(this);
			}			
		}

        public DataSet GetDataSet(string Query, Hashtable hsInputParameterValues)
        {
            DataSet objDataSet = new DataSet();
            StartDBTrace();

            try
            {
                Monitor.Enter(this);
                _objCommand.CommandType = CommandType.Text;
                _objCommand.CommandText = ReplaceDboInQuery(Query);
                _objCommand.CommandTimeout = _dbCommandTimeout;
                PrepareParameters(hsInputParameterValues);
                IDbDataAdapter objDataAdapter = GetDataAdapter();
                objDataAdapter.SelectCommand = _objCommand;
                objDataAdapter.Fill(objDataSet);
                return objDataSet;
            }
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
            finally
            {
                EndDBTrace(Query, hsInputParameterValues);
                Monitor.Exit(this);
            }           
        }

		/// <summary>
		/// Returns DataRow
		/// </summary>
		/// <param name="Query">input Query</param>
		/// <returns>DataRow</returns>
		public DataRow GetDataRow(string Query)
		{
			//if(!TransON)throw new Exception("WARNING!! You have not used the function BeginTrans() or you used a function CommitTrans()."); 

			DataTable  objDataTable = new DataTable();
			
			objDataTable = this.GetData(Query);

            if(objDataTable.Rows.Count > 0)
			{
				return objDataTable.Rows[0];
			}
			else
			{
				return null;
			}
		}

        public DataRow GetDataRow(string Query, Hashtable hsInputParameterValues)
        {
            DataTable objDataTable = GetData(Query, hsInputParameterValues);

            if (objDataTable != null && objDataTable.Rows.Count > 0)
                return objDataTable.Rows[0];
            else
                return null;
            
        }

	    /// <summary>
		/// Only Remove Single Quotes from the String
		/// </summary>
		/// <param name="Value"></param>
		/// <returns></returns>
		public string RSQx(string Value)
		{
			return Value.Replace("'","''");
		}

		/// <summary>
		/// Remove Single Quotes and Add single Quotes on Either Ends
		/// </summary>
		/// <param name="Value"></param>
		/// <returns></returns>
		public string RSQ(string Value)
		{
			return "'" + Value.Replace("'","''") + "'";
		}

		/// <summary>
		/// Add single Quotes on Either Ends
		/// </summary>
		/// <param name="Value"></param>
		/// <returns></returns>
		public string RSQ(DateTime Value)
		{
			return "'" + Value.ToString() + "'";
		}
			
		
		/// <summary>
		/// This method returns the IDataReader
		/// <param name="Query">QueryString</param>
		/// <returns>IDataReader</returns>
		/// </summary>
		public IDataReader  ExecuteQuery(string Query)
		{
			IDataReader objReader =null;
            StartDBTrace();

			try
			{
				Monitor.Enter(this);
				_objCommand.CommandType =CommandType.Text;
				_objCommand.CommandText = Query;
				objReader = _objCommand.ExecuteReader();
                return (objReader);	
			}
            catch (Exception e)
            {
                _lastException = e.Message;
                throw;
            }
			finally
			{
                EndDBTrace(Query, null);
				Monitor.Exit(this);
			}			
		}

        public string GetLastExecQuery()
        {
            if (_objCommand != null)
                return _objCommand.CommandText;
            else
                return string.Empty;
        }


		/// <summary>
		/// This method is only for SQL Server, will return SQLDataReader
		/// <param name="Query">QueryString</param>
		/// <returns>SqlDataReader</returns>
		/// </summary>
		public byte[] GetBinaryData(string Query)
		{
			byte[] barrImg = null;
								
			barrImg = (byte[])GetValue(Query);				
			
			return barrImg;	
		}


		/// <summary>
		/// This method is used to get the next ID for a table
		/// <param name="strObjectName">Table name to get the NextId</param>
		/// <returns>interger</returns>
		/// </summary>
		public int GetNextObjectID(string strObjectName)
		{
			int liNextId=-1 ;
			object ReturnValue = null;
			try
			{	
				//hashtable for input parameters
				Hashtable hsInputParameterValues = new Hashtable();
                hsInputParameterValues.Add("ObjectName", strObjectName);
				hsInputParameterValues.Add("BatchSize",1);
						
				//hashtable for the output parameters
				Hashtable hsOutputParameterValues = new Hashtable ();
				hsOutputParameterValues.Add("NextUniqueID",-1);
				Execute("GetNextUniqueObjectID", hsInputParameterValues, ref hsOutputParameterValues, ref ReturnValue);                
                liNextId = Convert.ToInt32(hsOutputParameterValues["NextUniqueID"].ToString());				
			}
			catch
			{
				throw;
			}
			return liNextId;	
		}


        /// <summary>
        /// This method is used to get the next ID for a table in the given range
        /// <param name="strObjectName">Table name to get the NextId</param>
        /// <returns>interger</returns>
        /// </summary>
        public int GetNextObjectIDInRange(string strObjectName,int min,int max)
        {
            int liNextId = -1;
            object ReturnValue = null;
            try
            {
                //hashtable for input parameters
                Hashtable hsInputParameterValues = new Hashtable();
                hsInputParameterValues.Add("ObjectName", strObjectName);
                hsInputParameterValues.Add("MIN", min);
                hsInputParameterValues.Add("MAX", max);
                
                //hashtable for the output parameters
                Hashtable hsOutputParameterValues = new Hashtable();
                hsOutputParameterValues.Add("NextUniqueID", -1);
                Execute("GetNextUniqueObjectIDInRange", hsInputParameterValues, ref hsOutputParameterValues, ref ReturnValue);
                liNextId = Convert.ToInt32(hsOutputParameterValues["NextUniqueID"].ToString());
            }
            catch
            {
                throw;
            }
            return liNextId;
        }

		/// <summary>
		/// The function returns all table names of database(only for SQLServer & Oracle).
		/// </summary>
		/// <returns></returns>
		
		public DataTable GetTables()
		{
			try
			{
				switch (_structdataSourceProvider)
				{
					case DBProvider.SQLServer: 
					{
						return GetData(SELECT_INFORMATION_SCHEMA);
					}
					case DBProvider.Oracle:
					{
						return GetData(SELECT_USER_TABLES);
					}
				}
			}
			catch
			{
				throw;
			}
			return null;
		}

        public DataTable GetTablesColumns(string strTableName)
        {
            try
            {
                switch (_structdataSourceProvider)
                {
                    case DBProvider.SQLServer:
                        {
                            return GetData(string.Format(SELECT_INFORMATION_SCHEMA_COULMNS, strTableName.ToUpper()));
                        }
                    case DBProvider.Oracle:
                        {
                            return GetData(string.Format(SELECT_USER_TABLES_COUMNS, strTableName.ToUpper()));
                        }
                }
            }
            catch
            {
                throw;
            }
            return null;
        }

        public string GetIsTableExist(string strTableName)
        {
            try
            {
                switch (_structdataSourceProvider)
                {
                    case DBProvider.SQLServer:
                        {
                            return GetValue(string.Format(SELECT_INFORMATION_SCHEMA_ISEXIST,strTableName.ToUpper())).ToString();
                        }
                    case DBProvider.Oracle:
                        {
                            return GetValue(string.Format(SELECT_USER_TABLES_ISEXIST, strTableName.ToUpper())).ToString();
                        }
                }
            }
            catch
            {
                throw;
            }
            return null;
        }
		
        public string GetIsConstraintExist(string strTableName)
        {
            try
            {
                switch (_structdataSourceProvider)
                {
                    case DBProvider.SQLServer:
                        {
                            return GetValue(string.Format(SELECT_INFORMATION_CONSTRAINT_ISEXIST, strTableName.ToUpper())).ToString();
                        }
                    case DBProvider.Oracle:
                        {
                            return GetValue(string.Format(SELECT_USER_CONSTRAINT_ISEXIST, strTableName.ToUpper())).ToString();
                        }
                }
            }
            catch
            {
                throw;
            }
            return null;
        }

        public string GetConstraintName (string strTableName)
        {
            string lstrreturnvalue = string.Empty;
            try
            {
                
                switch (_structdataSourceProvider)
                {
                    case DBProvider.SQLServer:
                        {
                            lstrreturnvalue = Convert.ToString(GetValue(string.Format(SELECT_INFORMATION_CONSTRAINT_NAME, strTableName.ToUpper())));
                            break;
                        }
                    case DBProvider.Oracle:
                        {
                            lstrreturnvalue = Convert.ToString(GetValue(string.Format(SELECT_user_CONSTRAINT_NAME, strTableName.ToUpper())));
                            break;
                        }
                }
            }
            catch
            {
                throw;
            }

            return lstrreturnvalue;
            
        }
        public string GetConstraintColumnName(string strTableName,string strConstrainName)
        {
            string lstrreturnvalue = string.Empty;
            try
            {
                switch (_structdataSourceProvider)
                {
                    case DBProvider.SQLServer:
                        {
                            lstrreturnvalue = Convert.ToString(GetValue(string.Format(SELECT_INFORMATION_CONSTRAINT_COLUMN_NAME, strTableName.ToUpper())));
                            break;
                        }
                    case DBProvider.Oracle:
                        {
                            lstrreturnvalue = Convert.ToString(GetValue(string.Format(SELECT_USER_CONSTRAINT_COLUMN_NAME, strTableName.ToUpper(), strConstrainName.ToUpper())));
                            break;
                        }
                }
            }
            catch
            {
                throw;
            }
            return lstrreturnvalue;            
        }


        public string GetIsTableCoulmnExist(string strTableName,string StrTableCoulmnName)
        {
            try
            {
                switch (_structdataSourceProvider)
                {
                    case DBProvider.SQLServer:
                        {
                            return GetValue(string.Format(SELECT_INFORMATION_SCHEMA_COULMNS_ISEXIST,strTableName.ToUpper(),StrTableCoulmnName.ToUpper())).ToString();
                        }
                    case DBProvider.Oracle:
                        {
                            return GetValue(string.Format(SELECT_USER_TABLES_COUMNS_ISEXIST, strTableName.ToUpper(),StrTableCoulmnName.ToUpper())).ToString();
                        }
                }
            }
            catch
            {
                throw;
            }
            return null;
        }

        public DataTable GetTableColumn(string lstrTableName)
        {
            try
            {
                switch (_structdataSourceProvider)
                {
                    case DBProvider.SQLServer:
                        {
                            return GetData(string.Format(SELECT_INFORMATION_TABLE_COLUMN, lstrTableName.ToUpper()));
                        }
                    case DBProvider.Oracle:
                        {
                            return GetData(string.Format(SELECT_USER_TABLE_COLUMN, lstrTableName.ToUpper()));
                        }
                }
            }
            catch
            {
                throw;
            }
            return null;
        }

		/// <summary>
		/// This function returns columns of specified table.
		/// </summary>
		/// <param name="TableName"></param>
		/// <returns></returns>
		public DataColumnCollection GetColumns(string TableName)
		{			
			if (string.Compare(TableName.Trim(),"Obj_Person",true) != 0)
			{
				DataTable dt = GetData(string.Format(SELECT_COLUMNS,TableName));
				return dt.Columns;
			}
			else
			{
				DataTable dt = new DataTable();
				DataColumnCollection dcc = dt.Columns;

				foreach (string stdField in QSecureHelper.StandardPersonFieldNames)
				{
					switch (stdField)
					{
						case "PhysObjectID" : 
							dcc.Add(stdField,typeof(int));
							break;
						case "Picture":
						case "Signature":
						case "Fingerprint":
							dcc.Add(stdField,typeof(byte[]));
							break;
						default:
							dcc.Add(stdField,typeof(string));
							break;
					}
				}
				
				return dcc;
			}
		}

        public bool GetIsView(string TableName)
        {
            bool result = false;
            try
            {
                switch (_structdataSourceProvider)
                {
                    case DBProvider.SQLServer:
                        {
                            if (Convert.ToString(GetValue(string.Format( SELECT_INFORMATION_SCHEMA_IsView, TableName.ToUpper()))) != "0")
                                result = true ;
                            break;
                        }
                    case DBProvider.Oracle:
                        {
                            if (Convert.ToString(GetValue(string.Format( SELECT_USER_TABLES_IsView, TableName.ToUpper()))) != "0")
                                return true;
                            break;
                        }
                }
            }
            catch
            {
                throw;
            }
            return result;
        }

        private void SetSessionContext()
        {
            if (!String.IsNullOrEmpty(Context))
            {
                if (_structdataSourceProvider == DBProvider.SQLServer)
                {
                    Execute("declare @userid varchar(64); declare @m binary(128); set @m = cast('" + _Context + "' as binary(128)); set context_info @m;");
                }
                else if (_structdataSourceProvider == DBProvider.Oracle)
                {
                    try
                    {
                        Execute("BEGIN dbms_session.set_identifier('" + _Context + "'); END;");
                    }
                    catch (Exception Ex)
                    {
                        string ERR = Ex.ToString();
                    }
                }
            }
        }

		#endregion Methods
	}

	#region Class specific for Oracle
	public class QSOracleServant
	{
		/// <summary>
		/// Creates an Instance of oracle data adapter
		/// </summary>
		/// <returns></returns>
		public static IDbDataAdapter GetDataAdapter()
		{
			IDbDataAdapter result = null;
			result = new OracleDataAdapter();
	
			return(result);
		}


		/// <summary>
		/// Prepares the parameters required to execute the store procedure
		/// </summary>
		/// <param name="objCommand"></param>
		/// <param name="refCursorTable"></param>
		/// <param name="hsInputParameterValues"></param>
		/// <param name="hsOutputParameterValues"></param>
		public static void PrepareParameters(ref IDbCommand objCommand,ref Hashtable refCursorTable, Hashtable hsInputParameterValues, ref Hashtable hsOutputParameterValues)
		{
			IDbDataParameter objParam = null;
			IDbDataParameter objOutParam = null;

			try
			{
				OracleCommand tempOracleCmd = (OracleCommand)objCommand;
				string paramName = string.Empty;
				OracleParameter refCursorParam = null;
				tempOracleCmd.BindByName = true;

				#region Caching Ref Cursor
				if(refCursorTable.Contains(tempOracleCmd.CommandText.ToUpper()))
				{
					paramName =  refCursorTable[tempOracleCmd.CommandText.ToUpper()].ToString();
				}
				else
				{
					OracleCommandBuilder.DeriveParameters(tempOracleCmd);

					for(int count = 0; count < tempOracleCmd.Parameters.Count; count++)
					{
						if(tempOracleCmd.Parameters[count].OracleDbType == OracleDbType.RefCursor)
						{
							paramName = tempOracleCmd.Parameters[count].ParameterName;								
							break;
						}							
					}						
					refCursorTable.Add(tempOracleCmd.CommandText.ToUpper(),paramName);
				}

				if(paramName != string.Empty)
				{
					refCursorParam = new OracleParameter(paramName,OracleDbType.RefCursor);
					refCursorParam.Direction = ParameterDirection.Output;
				}
				#endregion Caching Ref Cursor

				//Clearing the parameters after derive parameters
				objCommand.Parameters.Clear();

				//Looping through the Input Parameters
				foreach(DictionaryEntry objDictionaryEnum in hsInputParameterValues)
				{
					objParam = new OracleParameter(objDictionaryEnum.Key.ToString (),objDictionaryEnum.Value );
					objCommand.Parameters.Add(objParam);
				}

				//Looping through the Output Parameters
				foreach(DictionaryEntry objDictionaryEnum1 in hsOutputParameterValues)
				{
					objOutParam = new OracleParameter(objDictionaryEnum1.Key.ToString(),(OracleDbType) GetDBType (objDictionaryEnum1.Value ),GetMaxSize(objDictionaryEnum1.Value),ParameterDirection.Output ,false,0,0,null,DataRowVersion.Current ,null);
					objCommand.Parameters.Add(objOutParam);
				}

				if(refCursorParam != null)
				{
					objCommand.Parameters.Add(refCursorParam);
				}
			}
			catch
			{
				throw;
			}

			return;
		}


		/// <summary>
		/// Creates an instance of Oracle specific connection
		/// </summary>
		/// <param name="connectionString"></param>
		/// <returns></returns>
		public static IDbConnection GetConnection(string connectionString)
		{
			IDbConnection result = null;
			
            result = new OracleConnection(connectionString);
			return(result);
		}

        /// <summary>
        /// Clears the connection Pool associated with connection object
        /// </summary>
        /// <param name="connection"></param>
        public static void ClearPool(IDbConnection connection)
        {
            OracleConnection.ClearPool((OracleConnection)connection);
        }

		/// <summary>
		/// Prepares parameters for blob datatype
		/// </summary>
		/// <param name="objCommand"></param>
		/// <param name="hsBlobValues"></param>
		public static void PrepareBlobParameters(ref IDbCommand objCommand, Hashtable hsBlobValues)
		{
			IDbDataParameter objParam = null;
			try
			{
				OracleCommand tempOracleCmd = (OracleCommand) objCommand;
				tempOracleCmd.BindByName = true;
				foreach(DictionaryEntry objDictionaryEnum in hsBlobValues)
				{

					objCommand.CommandText = objCommand.CommandText.Replace("@" + objDictionaryEnum.Key.ToString(),":" + objDictionaryEnum.Key.ToString());	
					if ( objDictionaryEnum.Value.GetType().Name == "Byte[]" )
						objParam = new OracleParameter (objDictionaryEnum.Key.ToString (),OracleDbType.Blob ,((byte[])objDictionaryEnum.Value).Length ,ParameterDirection.Input,false,0,0,null,DataRowVersion.Current ,objDictionaryEnum.Value);
					else	
						objParam = new OracleParameter (objDictionaryEnum.Key.ToString (),(OracleDbType)GetDBType( objDictionaryEnum.Value ) ,objDictionaryEnum.Value.ToString().Length ,ParameterDirection.Input,false,0,0,null,DataRowVersion.Current ,objDictionaryEnum.Value);
					objCommand.Parameters.Add (objParam);
				}
			}
			catch
			{
				throw;
			}
			return;
		}

		/// <summary>
		///  Gets the OracleDbType for the corresponding input datatype
		/// </summary>
		/// <param name="objvalue"></param>
		/// <returns></returns>
		public static object GetDBType(object objvalue)
		{
			object ret = null;
			try
			{
				switch(objvalue.GetType().Name )
				{
					case "Int64": {ret = OracleDbType.Int64; break; }
					case "Int32": {ret = OracleDbType.Int32 ; break;}
					case "Int16": {ret = OracleDbType.Int16 ; break;}
					case "String": {ret = OracleDbType.Varchar2; break;}
					case "DateTime": {ret = OracleDbType.TimeStamp; break; }
					case "Boolean":{ret = OracleDbType.Char ; break; }
					case "Byte[]": {ret = OracleDbType.Byte; break; }
					case "NTextString" : {ret = OracleDbType.Clob; break; }
					default: {ret = OracleDbType.Varchar2 ; break;}
				}
			}
			catch
			{
				throw;
			}
			return(ret);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="objvalue"></param>
		/// <returns></returns>
		private static int GetMaxSize(object objvalue)
		{
			int ret = int.MaxValue;
			switch(objvalue.GetType().Name)
			{
				case "Int64": {ret = int.MaxValue; break;}
				case "Int32": {ret = int.MaxValue;	break;}
				case "Int16": {ret = short.MaxValue; break;}
				case "String": {ret = 8000; break;}
				case "DateTime": {ret = objvalue.ToString().Length; break;}
				case "Boolean": {ret = 10; break;}
				case "Byte[]": {ret =  ((byte[]) objvalue).Length ; break;}
				default: {ret = 8000; break;}
			}
			return ret;
		}

		public static void PrepareParamCollection(Hashtable hsInputOutputParameterValues, ref IDbCommand objCommand)
        {
			IDbDataParameter objParam = null;
            OracleCommand tempOracleCmd = (OracleCommand)objCommand;
            tempOracleCmd.BindByName = true;
			foreach (DictionaryEntry objDictionaryEnum in hsInputOutputParameterValues)
			{	
				objParam = new OracleParameter (objDictionaryEnum.Key.ToString (),objDictionaryEnum.Value);
				objCommand.Parameters.Add (objParam); 
			}

			return;
		}
       
 

	}
	#endregion
}


